﻿using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using DbMetadata.Providers.SqlServer;

namespace DbMetadata.Providers
{
    /// <summary>
    /// Provides a base class for implementing provider specific metadata providers.
    /// </summary>
    public class DbMetadataProvider : IDbMetadataProvider
    {
        private readonly IDbMetadataExtractor _dbMetadataExtractor;                        
        private readonly ITypeMapper _typeMapper;
        private IEnumerable<TableInfo> _tables;
        private IEnumerable<ColumnInfo> _columns;
        private IEnumerable<PrimaryKeyInfo> _primaryKeys;
        private IEnumerable<ForeignKeyInfo> _foreignKeys;

        /// <summary>
        /// Initializes a new instance of the <see cref="DbMetadataProvider"/> class.
        /// </summary>
        /// <param name="dbMetadataExtractor">The <see cref="IDbMetadataExtractor"/> instance that is used to retrieve metadata information.</param>
        /// <param name="typeMapper">The <see cref="ITypeMapper"/> instance that is used to map database types to .Net types.</param>
        internal DbMetadataProvider(IDbMetadataExtractor dbMetadataExtractor, ITypeMapper typeMapper)
        {
            _dbMetadataExtractor = dbMetadataExtractor;
            _typeMapper = typeMapper;
        }        

        private IList<DbTableInfo> CreateTables()
        {
            var dbTables = new List<DbTableInfo>();            
            

            foreach (var table in _tables)
            {
                var tableInfo = CreateTable(table);
                CreateColumns(tableInfo);
                dbTables.Add(tableInfo);
            }

            CreatePrimaryKeys(dbTables);
            CreateRelations(dbTables);
            return dbTables;
        }

        private void CreateColumns(DbTableInfo tableInfo)
        {
            IEnumerable<ColumnInfo> tableColumns = GetTableColumns(tableInfo);
            foreach (var tableColumn in tableColumns)
            {
                CreateColumn(tableInfo, tableColumn);
            }
        }

        private IEnumerable<ColumnInfo> GetTableColumns(DbTableInfo tableInfo)
        {
            return _columns.Where(
                c => c.TableName == tableInfo.TableName && c.TableSchema == tableInfo.TableSchema && tableInfo.TableCatalog == c.TableCatalog)
                .OrderBy(c => c.OrdinalPosition).ToList();
        }

        private void CreateColumn(DbTableInfo tableInfo, ColumnInfo tableColumn)
        {
            DbColumnInfo columnInfo = new DbColumnInfo();
            columnInfo.Table = tableInfo;
            columnInfo.ColumnName = tableColumn.ColumnName;
            columnInfo.DataType = _typeMapper.MapType(tableColumn.DataTypeName, tableColumn.IsNullable);
            columnInfo.OrdinalPosition = tableColumn.OrdinalPosition;
            columnInfo.IsNullable = tableColumn.IsNullable;
            tableInfo.Columns.Add(columnInfo.ColumnName, columnInfo);
        }

        private DbTableInfo CreateTable(TableInfo table)
        {
            DbTableInfo tableInfo = new DbTableInfo();

            tableInfo.TableSchema = table.TableSchema;
            tableInfo.TableName = table.TableName;
            tableInfo.TableCatalog = table.TableCatalog;
            tableInfo.TableType = table.TableType == "TABLE" ? DbTableType.Table : DbTableType.View;
            return tableInfo;
        }

       



        private void CreatePrimaryKeys(IEnumerable<DbTableInfo> tables)
        {            
            foreach (var tableInfo in tables)
            {
                var keyColumns =
                    _primaryKeys.Where(
                        p =>
                        p.TableSchema == tableInfo.TableSchema && p.TableName == tableInfo.TableName &&
                        p.TableCatalog == tableInfo.TableCatalog).Join(tableInfo.Columns, k => k.ColumnName, c => c.Key, (keyColumn, column) => new { KeyColumn = keyColumn, Column = column.Value }).OrderBy(c => c.Column.OrdinalPosition);

                foreach (var keyColumn in keyColumns)
                {
                    var keyInfo = tableInfo.PrimaryKey;
                    if (keyInfo == null)
                    {
                        keyInfo = new DbPrimaryKeyInfo();
                        keyInfo.Table = tableInfo;
                        keyInfo.Name = keyColumn.KeyColumn.ConstraintName;
                        tableInfo.PrimaryKey = keyInfo;
                    }
                    keyInfo.Columns.Add(keyColumn.Column);
                }

            }
        }


        private void CreateRelations(IEnumerable<DbTableInfo> tables)
        {
            
            IDictionary<string, DbRelationInfo> relations = new Dictionary<string, DbRelationInfo>();
            foreach (var tableInfo in tables)
            {
                //Find all foreign key columns and sort them by their constraint membership and ordinal position
                var keyColumns = _foreignKeys.Where(
                    f =>
                    f.ForeignTableCatalog == tableInfo.TableCatalog && f.ForeignTableSchema == tableInfo.TableSchema &&
                    f.ForeignTableName == tableInfo.TableName)
                    .Join(tableInfo.Columns, k => k.ForeignColumnName, c => c.Key,
                          (keyColumn, column) => new { KeyColumn = keyColumn, Column = column.Value }).OrderBy(
                    c => c.KeyColumn.ConstraintName).ThenBy(c => c.Column.OrdinalPosition);

                foreach (var keyColumn in keyColumns)
                {
                    //Find the primary table that this foreignkey column refers to.
                    var primaryTableInfo =
                        tables.Where(t => t.TableCatalog == keyColumn.KeyColumn.PrimaryTableCatalog
                                          && t.TableSchema == keyColumn.KeyColumn.PrimaryTableSchema &&
                                          t.TableName == keyColumn.KeyColumn.PrimaryTableName).
                            First();
                    if (!relations.ContainsKey(keyColumn.KeyColumn.ConstraintName))
                    {
                        var foreignKey = new DbForeignKeyInfo();
                        foreignKey.Name = keyColumn.KeyColumn.ConstraintName;
                        foreignKey.Table = tableInfo;
                        foreignKey.PrimaryTable = primaryTableInfo;
                        DbRelationInfo relationInfo = new DbRelationInfo();
                        relationInfo.PrimaryKey = tableInfo.PrimaryKey;
                        relationInfo.ForeignKey = foreignKey;

                        tableInfo.Relations.Add(relationInfo);
                        tableInfo.ForeignKeys.Add(foreignKey);


                        primaryTableInfo.Relations.Add(relationInfo);
                        relations.Add(keyColumn.KeyColumn.ConstraintName, relationInfo);
                    }
                    DbForeignKeyColumnInfo foreignKeyColumnInfo = new DbForeignKeyColumnInfo();
                    foreignKeyColumnInfo.ForeignColumn = keyColumn.Column;
                    foreignKeyColumnInfo.PrimaryColumn =
                        primaryTableInfo.Columns[keyColumn.KeyColumn.PrimaryColumnName];
                    foreignKeyColumnInfo.ForeignKey = relations[keyColumn.KeyColumn.ConstraintName].ForeignKey;
                    relations[keyColumn.KeyColumn.ConstraintName].ForeignKey.ForeignKeyColumns.Add(foreignKeyColumnInfo);
                }

            }
        }



        private void ReadMetadata()
        {
            _tables = _dbMetadataExtractor.ReadTables();
            _columns = _dbMetadataExtractor.ReadColumns();
            _primaryKeys = _dbMetadataExtractor.ReadPrimaryKeys();
            _foreignKeys = _dbMetadataExtractor.ReadForeignKeys();

        }

        /// <summary>
        /// Gets the metadata from the database.
        /// </summary>
        /// <returns><see cref="DbMetadataInfo"/></returns>
        public DbMetadataInfo GetMetadata()
        {
            ReadMetadata();
            DbMetadataInfo dbMetadataInfo = CreateMetadata();
            return dbMetadataInfo;
        }

        private DbMetadataInfo CreateMetadata()
        {
            var tables = CreateTables();            
            return new DbMetadataInfo {Tables = tables};
        }
       
    }
}
